with second_code as (
    select
        id
        ,code
        ,network_id
        ,network_name
        ,network_code
        ,country_id
        ,province_id
        ,city_id
        ,area_id
        ,town_id
        ,center_id
        ,center_name
        ,center_code
        ,center_province_id
        ,center_city_id
        ,center_area_id
        ,is_enable
        ,is_delete
        ,create_by
        ,update_by
        ,create_by_name
        ,update_by_name
        ,create_time
        ,update_time
        ,version
        ,sort
        ,modified_time
        ,effect_time
        ,invalid_time
        ,row_number() over(partition by code,center_code order by create_time desc) as row_id
    from jms_ods.yl_lmdm_sys_second_code
    where dt='{{ execution_date | cst_ds }}'
    and is_enable = 1 and is_delete = 1
)

insert overwrite table jms_dim.dim_lmdm_sys_second_code
select
    id
    ,code
    ,network_id
    ,network_name
    ,network_code
    ,country_id
    ,province_id
    ,city_id
    ,area_id
    ,town_id
    ,center_id
    ,center_name
    ,center_code
    ,center_province_id
    ,center_city_id
    ,center_area_id
    ,is_enable
    ,is_delete
    ,create_by
    ,update_by
    ,create_by_name
    ,update_by_name
    ,create_time
    ,update_time
    ,version
    ,sort
    ,modified_time
    ,effect_time
    ,invalid_time
from second_code
where row_id = 1
distribute by 1;